﻿using System.Collections.Generic;
using System.Data;
using Npgsql;
using System;

namespace PGMapper
{
    public class PostgresDatabase
    {
        [ThreadStatic]
        bool printSqlToConsole;

        // limited to one per database per thread!
        [ThreadStatic]
        PostgresTransaction transaction;

        internal PostgresTransaction CurrentTransaction { get { return transaction; } }

        public static PostgresDatabase SharedDatabase { get; set; }

        public string ConnectionString { get; private set; }
        public bool PrintSqlToConsole { get { return printSqlToConsole; } set { printSqlToConsole = value; } }

        public PostgresDatabase(string connectionString)
        {
            if (SharedDatabase == null)
                SharedDatabase = this;

            this.ConnectionString = connectionString;
        }

        /// <summary>
        /// Begins a transaction on this thread. All database interaction will be
        /// within the transaction until you call CommitTransaction() or
        /// RollbackTransaction(). You must do one or the other or the
        /// connection will be leaked!
        /// </summary>
        public void BeginTransaction()
        {
            if (transaction == null)
                transaction = new PostgresTransaction(GetConnection());

            transaction.Begin();
        }

        /// <summary>
        /// Commits the pending transaction.
        /// </summary>
        public void CommitTransaction()
        {
            if (transaction.Commit())
                transaction = null;
        }

        /// <summary>
        /// Rolls back the pending transaction.
        /// </summary>
        public void RollbackTransaction()
        {
            if (transaction.Rollback())
                transaction = null;
        }

        public NpgsqlConnection GetConnection()
        {
            // Rely on NpgsqlConnection's built-in pooling to ensure connection reuse
            return new NpgsqlConnection(ConnectionString);
        }

        public PostgresCommand CreateCommand(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters);
        }

        #region Shortcuts for PostgresCommand

        /// <summary>
        /// Executes a command that returns no data. Returns the number of rows affected by executing the command.
        /// </summary>
        public int Execute(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).Execute();
        }

        /// <summary>
        /// Executes a command that returns a single object.
        /// </summary>
        public object ExecuteScalar(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteScalar();
        }

        /// <summary>
        /// Executes a command that returns a single object of the given type.
        /// </summary>
        public T ExecuteSingle<T>(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteSingle<T>();
        }

        /// <summary>
        /// Executes a command that returns the result of a COUNT operation.
        /// </summary>
        public int ExecuteCount(string sql, params object[] positionalParameters)
        {
            return (int)ExecuteSingle<long>(sql, positionalParameters);
        }

        /// <summary>
        /// Returns a cursor-based data reader from the DB.  Remember to close it when
        /// you're finished!
        /// </summary>
        public IDataReader ExecuteReader(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteReader();
        }

        /// <summary>
        /// Executes the command and returns the first row as a map from column name to row value.
        /// </summary>
        public PostgresRow ExecuteFirstRow(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteFirstRow();
        }

        /// <summary>
        /// Executes the command and returns the first row as a map from column name to row value.
        /// If more than one row is found, an exception is thrown. If no rows were found, returns null.
        /// </summary>
        public PostgresRow ExecuteRow(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteRow();
        }

        /// <summary>
        /// Executes the command and returns the first row as a map from column name to row value.
        /// If more than one row is found, or if no rows were found, an exception is thrown.
        /// </summary>
        public PostgresRow ExecuteOneRow(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteOneRow();
        }

        /// <summary>
        /// Executes the command and loads the result into a DataTable.
        /// </summary>
        public DataTable ExecuteTable(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteTable();
        }

        /// <summary>
        /// Executes the command and loads the results of the first column into a strongly typed list.
        /// </summary>
        public List<T> ExecuteList<T>(string sql, params object[] positionalParameters)
        {
            return new PostgresCommand(this, sql, positionalParameters).ExecuteList<T>();
        }

        /// <summary>
        /// Executes the command and loads the results of the first column into a strongly typed array.
        /// </summary>
        public T[] ExecuteArray<T>(string sql, params object[] positionalParameters)
        {
            return ExecuteList<T>(sql, positionalParameters).ToArray();
        }

        #endregion
    }
}
